﻿Imports System.Data.OracleClient
Imports System.Configuration

Public Interface IAdministradorOracle
    'ellanos Inicio
    Sub SP_CreateInputOracle(ByVal name As String, ByVal value As Object, ByVal size As Int32)
    Sub SP_CreateOutputOracle(ByVal name As String, ByVal value As Object, ByVal size As Int32)
    'fin
End Interface

Public Class AdministradorOracle
    Implements IAdministradorOracle

    Private conexion As OracleConnection
    Private _command As OracleCommand
    Private _objParameter As PrimitiveParameter

    Sub New()
        _objParameter = New PrimitiveParameter
    End Sub


    'Ellanos inicio
    Public Sub CreateInputOracle(ByVal name As String, ByVal value As Object, ByVal size As Int32) Implements IAdministradorOracle.SP_CreateInputOracle
        _objParameter.SP_CreateInputOracle(name, value, size)
    End Sub

    Public Sub CreateOutputOracle(ByVal name As String, ByVal value As Object, ByVal size As Int32) Implements IAdministradorOracle.SP_CreateoutputOracle
        _objParameter.SP_CreateOutputOracle(name, value, size)
    End Sub
    'fin

    'Ellanos inicio
    Public Sub ExecuteOracle_NonQuery(ByVal psql As String, ByVal pcadena As String)
        Dim strCadena As String
        strCadena = ConfigurationManager.ConnectionStrings(pcadena).ToString()

        Dim connection As New OracleConnection(strCadena)
        Dim command As New OracleCommand()
        'Dim reader As OracleDataReader

        connection.Open()
        command.Connection = connection
        command.CommandText = psql
        command.CommandType = CommandType.StoredProcedure
        For i = 0 To _objParameter.Count - 1
            command.Parameters.Add(_objParameter.ItemOracle(i))
        Next
        'reader = command.ExecuteReader()
        Dim j As Int16 = command.ExecuteNonQuery()
        'While (reader.Read())
        '    ' Do something with the values.
        'End While
        'reader.Close()
        connection.Close()
    End Sub
    'fin

    Public Sub Conectar()
        Try
            Dim strCadena As String
            strCadena = ConfigurationManager.ConnectionStrings("Conexion.Oracle").ToString()
            conexion = New OracleConnection(strCadena)
        Catch ex As Exception
            'Dim objLog As New logDH
            'objLog.Registra_Excepcion(ex.Message, ex.Source & " - Sub Conectar")
            Throw ex
        End Try

    End Sub

    Public Sub ConectarOracle(ByVal cadena As String)
        Try
            Dim strCadena As String
            strCadena = ConfigurationManager.ConnectionStrings(cadena).ToString()
            conexion = New OracleConnection(strCadena)
        Catch ex As Exception
            'Dim objLog As New logDH
            'objLog.Registra_Excepcion(ex.Message, ex.Source & " - Sub ConectarMoneda")
            Throw ex
        End Try
    End Sub

    Public Function ExecuteSQL(ByVal psql As String, ByVal pcadena As String) As DataTable
        Dim cmd As OracleCommand = Nothing
        Dim da As OracleDataAdapter = Nothing
        Dim ds As DataSet

        Try
            ConectarOracle(pcadena)
            cmd = New OracleCommand()
            cmd.Connection = conexion
            cmd.CommandText = psql
            cmd.CommandType = CommandType.Text

            da = New OracleDataAdapter(cmd)
            ds = New DataSet()
            da.Fill(ds)

            Return ds.Tables(0)
            'Return OrdenarTabla(ds.Tables(0), "ITEM_ORD ASC, FECHA_OPERACION DESC")
        Catch ex As Exception
            'Dim objLog As New logDH
            'objLog.Registra_Excepcion(ex.Message, ex.Source & " - Function ExecuteSQL")
            Throw ex
        Finally
            If (Not da Is Nothing) Then da.Dispose()
            If cmd.Connection.State = ConnectionState.Open Then
                cmd.Connection.Close()
            End If
            If conexion.State = ConnectionState.Open Then
                conexion.Close()
            End If
            If Not conexion Is Nothing Then conexion.Dispose()
            conexion = Nothing
            cmd = Nothing
        End Try
    End Function

    Public Function ExecuteOracle(ByVal psql As String, ByVal pcadena As String) As DataTable
        Dim cmd As OracleCommand = Nothing
        Dim da As OracleDataAdapter = Nothing
        Dim ds As DataSet

        Try
            Dim strCadena As String
            strCadena = ConfigurationManager.ConnectionStrings(pcadena).ToString()
            Dim connection As New OracleConnection(strCadena)
            ds = New DataSet()
            cmd = New OracleCommand
            connection.Open()
            cmd.Connection = connection
            cmd.CommandText = psql
            cmd.CommandType = CommandType.StoredProcedure
            For i = 0 To _objParameter.Count - 1
                cmd.Parameters.Add(_objParameter.ItemOracle(i))
            Next
            'cmd.Parameters.Add(New OracleParameter("p_Cursor", OracleType.Cursor)).Direction = ParameterDirection.Output
            da = New OracleDataAdapter(cmd)
            da.Fill(ds)
            Return ds.Tables(0)

        Catch ex As Exception
           Throw ex
        Finally
            
            'If Not conexion Is Nothing Then conexion.Dispose()
            conexion = Nothing
            cmd = Nothing
        End Try
    End Function

    Private Sub parametroChar(ByRef command As OracleCommand, ByVal parametro As String, ByVal valor As String)
        Try
            If String.IsNullOrEmpty(valor) Or valor = Nothing Then
                command.Parameters.Add(parametro, OracleType.Char).Value = DBNull.Value
            Else
                command.Parameters.Add(parametro, OracleType.Char).Value = valor
            End If
        Catch ex As Exception
            'Dim objLog As New logDH
            'objLog.Registra_Excepcion(ex.Message, ex.Source & " - Sub parametroChar")
            Throw ex
        End Try
    End Sub

    Private Sub parametroInteger(ByRef command As OracleCommand, ByVal parametro As String, ByVal valor As Integer)
        Try
            If valor = Nothing Then
                command.Parameters.Add(parametro, OracleType.Number).Value = DBNull.Value
            Else
                command.Parameters.Add(parametro, OracleType.Number).Value = valor
            End If
        Catch ex As Exception
            'Dim objLog As New logDH
            'objLog.Registra_Excepcion(ex.Message, ex.Source & " - Sub parametroInteger")
            Throw ex
        End Try
    End Sub

    Private Sub parametroLong(ByRef command As OracleCommand, ByVal parametro As String, ByVal valor As Long)
        Try
            If valor = Nothing Then
                command.Parameters.Add(parametro, OracleType.Number).Value = DBNull.Value
            Else
                command.Parameters.Add(parametro, OracleType.Number).Value = valor
            End If
        Catch ex As Exception
            'Dim objLog As New logDH
            'objLog.Registra_Excepcion(ex.Message, ex.Source & " - Sub parametroLong")
            Throw ex
        End Try
    End Sub

    Private Sub parametroDouble(ByRef command As OracleCommand, ByVal parametro As String, ByVal valor As Double)
        Try
            If valor = Nothing Then
                command.Parameters.Add(parametro, OracleType.Number).Value = DBNull.Value
            Else
                command.Parameters.Add(parametro, OracleType.Number).Value = valor
            End If
        Catch ex As Exception
            'Dim objLog As New logDH
            'objLog.Registra_Excepcion(ex.Message, ex.Source & " - Sub parametroDouble")
            Throw ex
        End Try
    End Sub

End Class
